﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Web;
using FeedAgregator;
using System.Web.Caching;
using System.Collections;

/// <summary>
/// Summary description for DataAccessHelper
/// </summary>
public class DataAccessHelper
{

    public const string AllCategoriesKey = "AllCategoriesKey";
    public const string AllTagsKey = "AllTagsKey";
    public const string TotalNewsNumberKey = "TotalNewsNumberKey";
    public const string Last24NewsNumberKey = "Last24NewsNumberKey";
    public const string MostReadedKey = "MostReadedKey";
    public const string LastBusinessKey = "LastBusinessKey";
    public const string LastFinanceKey = "LastFinanceKey";
    public const string LastAutomotiveKey = "LastAutomotiveKey";
    public const string LastBlogsKey = "LastBlogsKey";
    public const string ItemsWithImagesKey = "ItemsWithImagesKey";

    public static List<FeedCategory> GetAllCategories()
    {
        List<FeedCategory> result;
        Cache cache = HttpRuntime.Cache;
        if (cache[AllCategoriesKey] == null)
        {
            using (FeedSignalDataContext ctx = new FeedSignalDataContext())
            {
                result = (from cats in ctx.FeedCategories
                          select cats).ToList();
            }
            cache.Add(AllCategoriesKey, result, null, DateTime.Now.AddMinutes(5), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[AllCategoriesKey] as List<FeedCategory>;
        }
        return result;
    }

    public static String GetCategoryTitleById(int catId)
    {
        string result = (from cats in GetAllCategories()
                         where cats.ID == catId
                         select cats.CategoryName).Single();
        return result;
    }

    public static Feed GetFeedByID(int feedId)
    {
        Feed result = null;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            result = (from feeds in ctx.Feeds
                      where feeds.ID == feedId
                      select feeds).SingleOrDefault();

        }
        return result;
    }

    public static List<Tag> GetAllTags()
    {
        Cache cache = HttpRuntime.Cache;
        List<Tag> result;
        if (cache[AllTagsKey] == null)
        {
            result = GetAllTagsFromDatabase();
            cache.Add(AllTagsKey, result, null, DateTime.Now.AddMinutes(120), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[AllTagsKey] as List<Tag>;
        }
        return result;
    }

    private static List<Tag> GetAllTagsFromDatabase()
    {
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            List<Tag> allTags = new List<Tag>();

            var tagsQuery = from tags in ctx.Tags
                            orderby tags.FeedItemTagRelations.Count() descending
                            select new { TagName = tags.TagName, Weight = tags.FeedItemTagRelations.Count() };
            foreach (var item in tagsQuery)
            {
                Tag tagToAdd = new Tag();
                tagToAdd.TagName = item.TagName;
                tagToAdd.Weight = item.Weight;
                allTags.Add(tagToAdd);
            }


            return allTags;
        }
    }



    public static FeedItem GetFeedItemByID(int feedItemID)
    {
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where items.ID == feedItemID
                        select items;
            return query.SingleOrDefault();




            //var query = from items in ctx.FeedItems
            //            where (from o in ctx.FeedItemCategoryRelations
            //                   where o.CategoryID == feedItemID
            //                   select o.FeedItemID).Contains(feedItemID) 
            //;



        }
    }

    public static long GetTotalNewsNumber()
    {
        Cache cache = HttpRuntime.Cache;
        long result;
        if (cache[TotalNewsNumberKey] == null)
        {
            result = GetTotalNewsNumberFromDataBase();
            cache.Add(TotalNewsNumberKey, result, null, DateTime.Now.AddMinutes(120), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = (long)cache[TotalNewsNumberKey];
        }
        return result;
    }

    private static long GetTotalNewsNumberFromDataBase()
    {
        long result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        select items;

            result = query.Count();
        }
        return result;
    }



    public static long GetLast24NewsNumber()
    {
        Cache cache = HttpRuntime.Cache;
        long result;
        if (cache[Last24NewsNumberKey] == null)
        {
            result = GetLast24NewsNumberFromDataBase();
            cache.Add(Last24NewsNumberKey, result, null, DateTime.Now.AddMinutes(30), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = (long)cache[Last24NewsNumberKey];
        }
        return result;
    }

    private static long GetLast24NewsNumberFromDataBase()
    {
        long result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where items.PubDate > DateTime.Now.AddHours(-24)
                        select items;
            result = query.Count();
        }
        return result;
    }

    public static List<FeedItem> GetMostReaded(int categoryId)
    {
        Cache cache = HttpRuntime.Cache;
        List<FeedItem> result;
        string cacheKey = MostReadedKey + categoryId.ToString();
        if (cache[MostReadedKey] == null)
        {
            result = GetMostReadedFromDataBase(categoryId);
            if (result.Count == 0)
            {
                result = GetMostReadedFromDataBase(0);
            }
            cache.Add(MostReadedKey, result, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[MostReadedKey] as List<FeedItem>;
        }
        return result;

    }

    private static List<FeedItem> GetMostReadedFromDataBase(int categoryId)
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where (items.Feed.FeedCategory.ID == categoryId || categoryId == 0)
                        select items;
            query = query.OrderByDescending(item => item.PubDate.Date).ThenByDescending(item => item.ReadHistories.Count());

            result = query.Take(7).ToList();
        }
        return result;

    }

    public static List<FeedItem> GetLastBusiness()
    {
        Cache cache = HttpRuntime.Cache;
        List<FeedItem> result;
        if (cache[LastBusinessKey] == null)
        {
            result = GetLastBusinessFromDataBase();
            cache.Add(LastBusinessKey, result, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[LastBusinessKey] as List<FeedItem>;
        }
        return result;
    }

    private static List<FeedItem> GetLastBusinessFromDataBase()
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where items.Feed.CategoryID == CategoryIds.Business
                        orderby items.PubDate descending
                        select items;
            result = query.Take(7).ToList();
        }
        return result;

    }

    public static List<FeedItem> GetLastFinance()
    {
        Cache cache = HttpRuntime.Cache;
        List<FeedItem> result;
        if (cache[LastFinanceKey] == null)
        {
            result = GetLastFinanceFromDataBase();
            cache.Add(LastFinanceKey, result, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[LastFinanceKey] as List<FeedItem>;
        }
        return result;
    }

    private static List<FeedItem> GetLastFinanceFromDataBase()
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where items.Feed.CategoryID == CategoryIds.Finance
                        orderby items.PubDate descending
                        select items;
            result = query.Take(7).ToList();
        }
        return result;

    }

    public static List<FeedItem> GetLastBlogs()
    {
        Cache cache = HttpRuntime.Cache;
        List<FeedItem> result;
        if (cache[LastBlogsKey] == null)
        {
            result = GetLastBlogsFromDataBase();
            cache.Add(LastBlogsKey, result, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[LastBlogsKey] as List<FeedItem>;
        }
        return result;
    }

    private static List<FeedItem> GetLastBlogsFromDataBase()
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where items.Feed.CategoryID == CategoryIds.Blogs
                        orderby items.PubDate descending
                        select items;
            result = query.Take(7).ToList();
        }
        return result;

    }

    public static List<FeedItem> GetLastAutomotive()
    {
        Cache cache = HttpRuntime.Cache;
        List<FeedItem> result;
        if (cache[LastAutomotiveKey] == null)
        {
            result = GetLastAutomotiveFromDataBase();
            cache.Add(LastAutomotiveKey, result, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[LastAutomotiveKey] as List<FeedItem>;
        }
        return result;
    }

    private static List<FeedItem> GetLastAutomotiveFromDataBase()
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where items.Feed.CategoryID == CategoryIds.Automotive
                        orderby items.PubDate descending
                        select items;
            result = query.Take(7).ToList();
        }
        return result;

    }


    struct CategoryIds
    {
        public const int Business = 63;
        public const int Finance = 82;
        public const int Blogs = 85;
        public const int Automotive = 89;
        public const int Sport = 79;

    }

    public static List<FeedItem> GetItemsWithImages(int categoryId)
    {
        Cache cache = HttpRuntime.Cache;
        List<FeedItem> result;
        string cacheKey = ItemsWithImagesKey + categoryId.ToString();
        if (cache[cacheKey] == null)
        {
            result = GetItemsWithImagesFromDataBase(categoryId);
            if (result.Count == 0 && categoryId != 0)
            {
                result = GetItemsWithImages(0);
            }
            cache.Add(cacheKey, result, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
        }
        else
        {
            result = cache[cacheKey] as List<FeedItem>;
        }
        return result;
    }


    public static List<FeedItem> GetItemsWithImagesFromDataBase(int categoryId)
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where items.ItemContent.Contains(@"<img") &&
                        items.ItemContent.Contains(@"src=""http://")
                        &&
                        (items.Feed.FeedCategory.ID == categoryId || categoryId == 0)
                        orderby items.PubDate descending
                        select items;
            if (query.Count() >= 7)
            {
                result = query.Take(7).ToList();
            }
            else
            {
                result = query.ToList();
            }

        }
        return result;
    }

    public static List<FeedItem> GetRelatedPosts(FeedItem item)
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            //ctx.FeedItems.Attach(item);
            List<string> threeRelatedWords = GetThreeWordsFromTitle(item.Title);

            var query = from items in ctx.FeedItems
                        where items.ID != item.ID &&
                        (items.Title.Contains(threeRelatedWords[0]) ||
    items.Title.Contains(threeRelatedWords[1]) ||
    items.Title.Contains(threeRelatedWords[2]))
                        orderby items.PubDate descending
                        select items;
            result = query.Take(7).ToList();
            if (result.Count < 7)
            {
                var altQuery = from items in ctx.FeedItems
                               where items.ID != item.ID &&
                               items.Feed.FeedCategory.ID == item.Feed.FeedCategory.ID
                               orderby items.PubDate descending
                               select items;
                result = altQuery.Take(7).ToList();
            }
        }
        return result;
    }

    private static List<string> GetThreeWordsFromTitle(string p)
    {
        string[] wordsInTitle = p.Split(new char[] { ' ' });
        List<string> result = new List<string>();
        var sortedWords =
             from w in wordsInTitle
             orderby w.Length descending
             select w;

        foreach (string stringItem in sortedWords)
        {
            if (stringItem.Length > 2)
            {
                result.Add(stringItem);
            }
            if (result.Count == 3)
            {
                break;
            }
        }
        if (result.Count == 1)
        {
            result.Add("daksjdkjasdlkasjkldas");
            result.Add("daksjdkjasdlkasjkldas");
        }
        if (result.Count == 2)
        {
            result.Add("kajfklsjflkjdsalkfsd");
        }

        return result;


    }




    public static List<FeedItem> GetForFeed()
    {
        List<FeedItem> result;
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        orderby items.PubDate descending
                        select items;
            result = query.Take(15).ToList();
        }
        return result;
    }

    public static string GetExternalCategoryTitleById(int categoryId)
    {
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItemCategories
                        where items.ID == categoryId
                        select items;
            FeedItemCategory result = query.Single();
            return result.CategoryName;
        }
    }

    public static object GetFeedItemsByExternalCategoryIdAndPageNumber(int CategoryId, int PageNumber, int pageSize)
    {
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItems
                        where (from r in ctx.FeedItemCategoryRelations
                               where r.CategoryID == CategoryId
                               select r.FeedItemID).Contains(items.ID)
                        orderby items.PubDate descending
                        select new { items.ID, items.PubDate, items.Title, items.Feed };
            int recordsToSkip = 0;
            if (PageNumber > 0)
            {
                PageNumber--;
            }
            recordsToSkip = PageNumber * pageSize;
            object result = query.Skip(recordsToSkip).Take(pageSize).ToList();
            return result;
        }
    }

    public static object GetExternalTagsFromDatabase(int categoryId)
    {
        using (FeedSignalDataContext ctx = new FeedSignalDataContext())
        {
            var query = from items in ctx.FeedItemCategories
                        join relations in ctx.FeedItemCategoryRelations on items.ID equals relations.CategoryID
                        join newsitems in ctx.FeedItems on relations.FeedItemID equals newsitems.ID
                        join feeds in ctx.Feeds on newsitems.FeedID equals feeds.ID
                        join cats in ctx.FeedCategories on feeds.CategoryID equals cats.ID
                        where cats.ID == categoryId || categoryId == 0
                        group items by new { items.CategoryName, items.ID, Weight = items.FeedItemCategoryRelations.Count } into g
                        select new { TagName = g.Key.CategoryName, TagID = g.Key.ID, Weight = g.Key.Weight };
            object result = query.OrderByDescending(a => a.Weight).Take(40).ToList();
            return result;


            //var query = from psc in prodDtcx.ProductSubcategory
            //            join p in prodDtcx.Product on psc.ProductSubcategoryID equals p.ProductSubcategoryID
            //            group psc by new { psc.Name, p.Color } into g
            //            select new { Color = g.Key.Color, ProductSubcategoryName = g.Key.Name };


        }
    }


    //public static List<FeedItemCategory> GetExternalTags()
    //{
    //    string cacheKey = "GetExternalTagsKey";
    //    string cacheKey = ItemsWithImagesKey + categoryId.ToString();
    //    if (cache[cacheKey] == null)
    //    {
    //        result = GetItemsWithImagesFromDataBase(categoryId);
    //        if (result.Count == 0)
    //        {
    //            result = GetItemsWithImages(0);
    //        }
    //        cache.Add(cacheKey, result, null, DateTime.Now.AddMinutes(60), Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);
    //    }
    //    else
    //    {
    //        result = cache[cacheKey] as List<FeedItem>;
    //    }
    //}
}
